# coding=utf-8

import web
from web.utils import storage

import utils
from db import connection

db = connection.default

def update_subscribe_lastsend(sid, last_send):
    """更新订阅时间最后发送时间"""
    return db.update('user_subscribe'
            , last_send = last_send
            , where = 'id = $sid'
            , vars = locals())

def get_subscribes(date):
    """获取所有订阅人"""
    return db.select('user_subscribe'
            , where = 'last_send < $date'
            , vars = locals())

def get_last_discount_top10(top = 10):
    """
    获取最新的前@top条甄选TOP10信息
    """
    sets = db.select('discount_top10', order = 'date_index DESC'
            , limit = top)
    return [r for r in sets]

def get_last_TOP10(top = 1, type = 0, date = 0, depart = 0, lightning = False, include_content = False):
    """
    获取最新的【甄选TOP10】信息
    """
    if date > 0:
        # 获取给定日期的甄选记录
        tset = get_last_discount_top10_by_date(date)
    else:
        # 获取最新设置的前top条甄选记录
        tset = get_last_discount_top10(top)
    if tset:
        # 将设置的的信息收集起来
        idlst = [t.discount_info_ids for t in tset if t.discount_info_ids]
        if idlst:
            # 组合成一条字符串
            # "这里为了可读性，没有将两条语句合并在一起写
            idlst = ','.join(idlst)
            # 读取出这些id的特惠信息记录
            posts = get_discounts_by_ids_with_dest(idlst
                        , type
                        , depart
                        , lightning
                        , include_content)
            # 转换为词典，便于按id取出每条特惠信息记录
            if posts:
                # 转换为词典
                pdict = { post.id: post for post in posts }
                # 创建返回列表
                retlst = []
                # 处理结果中的所有甄选信息
                while tset:
                    # 从特惠甄选信息中取出一条
                    dist = tset.pop(0)
                    # 切割设置的特惠信息id
                    if dist.discount_info_ids:
                        # 创建当期列表
                        ids = dist.discount_info_ids.split(',')
                        # 添加当前设置的特惠信息列表到返回结果集中
                        lst = [pdict[int(id)] for id in ids if int(id) in pdict]
                        # "以上代码为了可读性没有合并为一句代码
                        # 列表按性价比排序
                        retlst.append(storage(date = dist.date_index
                            , lst = sorted(lst
                                        , key = lambda p: p.price_index
                                        , reverse = True)
                            ))

                # 返回结果
                # 前台调用此接口后，可以通过列表索引，
                # 获取对应周数的甄选特惠信息列表
                return retlst

    # 到这里处理失败了
    # 1) 可能没有设置甄选信息
    # 2) 可能甄选信息中没有设置特惠信息
    return None

def get_discounts_by_ids_with_dest(ids, type = 0, depart = 0, lightning = False, include_content = False):
    """
    获取给定id列表的特惠信息（包含目的地名）
    """
    if ids:
        att_where = ''
        att_proj_where = ''
        if type:
            #att_from = '''INNER JOIN 
            #              travel_proj AS tp 
            #                ON tp.id = di.proj_id AND tp.type = $type
            #        '''
            att_proj_where = ' AND tp.type = $type'
        # 给定了出发城市
        if depart:
            if isinstance(depart, (int, long)):
                att_where = ' AND di.depart_id = $depart'
            elif isinstance(depart, tuple):
                att_where = ' AND di.depart_id IN $depart'
        # 闪电特惠
        if lightning:
            att_where = ' AND di.lightning = 1'

        att_from = ''
        att_fields = ''
        if include_content:
            # 要在返回结果中包含特惠信息的内容
            # 那这里就要再联合detail表
            att_from = '''INNER JOIN
                          discount_detail_info AS ddi 
                            ON ddi.id = di.id
                    '''
            att_fields = ',ddi.content'

        return db.query('''
                SELECT di.id,di.title,di.price,di.discount,
                        di.media_id,di.price_index,di.is_initial_price,
                        di.lightning,di.directing,
                        IFNULL(dest.name, tp.name) AS dest_name,p.name AS partner_name, 
                        tp.type%(fields)s
                FROM discount_info AS di
                    INNER JOIN
                    partner AS p
                        ON p.id = di.partner_id
                    INNER JOIN
                    travel_proj AS tp
                        ON tp.id = di.proj_id%(proj_where)s
                    LEFT OUTER JOIN
                    travel_dest AS dest
                        ON dest.id = tp.dest_id
                    %(from)s
                WHERE di.id IN (%(ids)s)%(where)s
                ORDER BY di.id DESC;
            ''' % {
                    'fields':att_fields,
                    'from':att_from,
                    'proj_where':att_proj_where,
                    'ids':ids,
                    'where':att_where,}
            , vars = locals())
    return None

def delete_discount_by_id(id):
    """
    删除给定的特惠信息
    """
    with db.transaction():
        # 删除 discount_detail_info
        db.delete('discount_detail_info'
                , where = 'id = $id'
                , vars = locals())
        # 删除 discount_info
        return db.delete('discount_info'
                , where = 'id = $id'
                , vars = locals())

    return False

def get_discount_top10s(offset, length):
    """
    获取 top10 列表
    """
    sets = db.query('''
                SELECT SQL_CALC_FOUND_ROWS * 
                FROM discount_top10
                ORDER BY date_index DESC
                LIMIT $offset, $length;
            '''
            , vars = locals())
    # 获取总数
    totals = db.query('SELECT FOUND_ROWS() AS totals');
    # returns
    return storage(result = sets, totalrecords = totals[0].totals)

def get_discount_top10(date):
    """
    获取给定的TOP10信息
    """
    sets = db.select('discount_top10', where = 'date_index = $date'
            , vars = locals())
    if sets:
        return sets[0]
    return None

def update_discount_top10(date, form):
    """
    更新特惠信息TOP10
    """
    return db.update('discount_top10'
            , date_index = form.date.value
            , discount_info_ids = form.discount_info_ids.value
            , where = 'date_index = $date'
            , vars = locals())

def add_discount_top10(form):
    """
    添加特惠信息TOP10
    """
    return db.insert('discount_top10'
            , date_index = form.date.value
            , discount_info_ids = form.discount_info_ids.value)

def get_discounts_by_ids(ids):
    """
    获取给定id列表的特惠信息集合
    """
    if ids:
        return db.select('discount_info', where = 'id IN (' + ids + ')')
    return None

def get_discounts(offset, length):
    """
    分页获取特惠信息列表
    """
    # 读取记录列表
    sets = db.query("""
            SELECT SQL_CALC_FOUND_ROWS di.id,di.url,di.title,di.media_id,
                di.date_created,
                proj.name AS proj_name,p.name AS partner_name,
                IFNULL(dest.name, '--') AS dest_name
            FROM discount_info AS di 
                INNER JOIN 
                travel_proj AS proj 
                    ON proj.id = di.proj_id 
                INNER JOIN 
                partner AS p 
                    ON p.id = di.partner_id 
                LEFT OUTER JOIN
                travel_dest AS dest
                    ON dest.id = proj.dest_id
            ORDER BY di.id DESC
            LIMIT $offset, $length;"""
            , vars = locals())
    # 读取总记录数
    totals = db.query('SELECT FOUND_ROWS() AS totals;')
    # 返回结果
    return storage(result = sets, totalrecords = totals[0].totals)

def get_discount_by_id(id):
    """
    获取指定的特惠信息记录
    @id as int, 特惠信息内码
    """
    sets = db.query("""
            SELECT di.*,ddi.content, ddi.booking_tips
            FROM discount_info AS di
                INNER JOIN
                discount_detail_info AS ddi
                    ON ddi.id = di.id
            WHERE di.id = $id;
            """
            , vars = locals())
    if sets:
        return sets[0]
    return None

def update_discount_by_form(id, form):
    """
    更新一条特惠记录
    """
    with db.transaction():
        # 更新 discount_info 表
        db.update('discount_info'
                , proj_id = form.proj_id.value
                , partner_id = form.partner_id.value
                #, dest_id = form.dest_id.value
                , url = form.url.value
                , expire = form.expire.value
                , title = form.title.value
                , price = form.price.value
                , discount = form.discount.value
                , price_index = form.price_index.value
                , is_initial_price = form.is_initial_price.checked and 1 or 0
                , lightning = form.lightning.checked and 1 or 0
                , directing = form.directing.checked and 1 or 0
                , depart_id = form.depart_id.value
                #, short_text = form.short_text.value
                , media_id = form.media_id.value
                , last_updated = web.SQLLiteral('NOW()')
                , where = 'id = $id'
                , vars = locals())
        # 更新 discount_detail_info 表
        db.update('discount_detail_info'
                , content = form.content.value
                , booking_tips = form.booking_tips.value
                , where = 'id = $id'
                , vars = locals())
        # 保存成功
        return True

def add_discount_by_form(form):
    """
    添加一条特惠信息记录
    @form as discount_form, 特惠信息录入的表单

    returns:
        insert_id as int
    """
    insert_id = 0
    # 添加特惠信息，涉及到对两个表的修改
    # 所以这里使用事务
    with db.transaction():
        # 添加 discount_ifno 获得 insert_id
        insert_id = db.insert('discount_info'
                , proj_id = form.proj_id.value
                , partner_id = form.partner_id.value
                #, dest_id = form.dest_id.value
                , url = form.url.value
                , expire = form.expire.value
                , title = form.title.value
                , price = form.price.value
                , discount = form.discount.value
                , price_index = form.price_index.value
                , is_initial_price = form.is_initial_price.checked and 1 or 0
                , lightning = form.lightning.checked and 1 or 0
                , directing = form.directing.checked and 1 or 0
                , depart_id = form.depart_id.value
                #, short_text = form.short_text.value
                , media_id = form.media_id.value
                , date_created = web.SQLLiteral('NOW()')
                , last_updated = web.SQLLiteral('NOW()'))
        # 添加discount_detail_info
        if insert_id > 0:
            db.insert('discount_detail_info'
                    , id = insert_id
                    , booking_tips = form.booking_tips.value
                    , content = form.content.value)
            # 保存成功
            # 返回保存的信息内码
            return insert_id
        # 保存失败
        return 0
